﻿using System;
using System.Collections.Generic;
using System.Text;
using Spring.Data.NHibernate;
using NHibernate;
using NHibernate.Transform;
using System.Reflection;
using System.Data;

namespace Commons.BaseUtil
{
    public class SqlDalSupport
    {
        private HibernateTemplate _template;

        public HibernateTemplate Template
        {
            set { _template = value; }
            get
            {
                return this._template;
            }
        }


        /// <summary>
        /// 返回传入SQL语句的结果集数目.
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int SqlStatementCount(string sql)
        {
            return SqlStatementCount(sql, null);
        }

        public virtual int SqlStatementCount(string sql, IDictionary<string, object> parameters)
        {
            int count = 0;
            Template.Execute(delegate(ISession session)
            {
                string rSql = session.GetNamedQuery("sqlCount").QueryString;
                rSql = RemoveOrderBy(rSql.Replace(":stement", sql));
                IQuery query = session.CreateSQLQuery(rSql);
                SetParams(query, parameters);
                count = query.UniqueResult<int>();
                return count;
            });
            return count;
        }

        /// <summary>
        /// 返回用户配置SQL语句的结果集数目.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <returns></returns>
        public virtual int NamedSqlRowCount(string sqlName)
        {
            return SqlStatementCount(GetSqlStatement(sqlName));
        }

        public virtual int NamedSqlRowCount(string sqlName, IDictionary<string, object> parameters)
        {
            return SqlStatementCount(GetSqlStatement(sqlName), parameters);
        }

        /// <summary>
        /// 返回用户配置SQL语句
        /// </summary>
        /// <param name="sqlName"></param>
        /// <returns></returns>
        public virtual string GetSqlStatement(string sqlName)
        {
            string sql = string.Empty;
            Template.Execute(delegate(ISession session)
            {
                sql = session.GetNamedQuery(sqlName).QueryString;
                return sql;
            });
            return sql;
        }

        public virtual IList<System.Collections.IDictionary> GetNamedSqlData(string sqlName)
        {
            return GetNamedSqlData(sqlName, null);
        }

        /// <summary>
        /// 批量执行SQL语句, 返回执行的行数.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecuteNamedSql(string sqlName, IList<IDictionary<string, object>> paramList)
        {
            int re = -1;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.GetNamedQuery(sqlName);
                foreach (IDictionary<string, object> parameters in paramList)
                {
                    SetParams(query, parameters);
                    re += query.ExecuteUpdate();
                }
                return re;
            });
            return re;
        }

        /// <summary>
        /// 使用一个模型对象实体，执行一句SQL语句, 并返回执行的行数.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int ExecuteNamedSql(string sqlName, object model)
        {
            return ExecuteNamedSql(sqlName, GetParams(model));
        }

        /// <summary>
        /// 执行一句SQL语句, 返回执行的行数.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecuteNamedSql(string sqlName, IDictionary<string, object> parameters)
        {
            int re = -1;
            Template.Execute(delegate(ISession session) {
                IQuery query = session.GetNamedQuery(sqlName);
                SetParams(query, parameters);
                re = query.ExecuteUpdate();
                return re;
            });
            return re;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteSql(string sql)
        {
            return ExecuteSql(sql, new object());
        }
        /// <summary>
        /// 执行SQL语句并传入模型参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteSql(string sql, object model)
        {
            return ExecuteSql(sql, GetParams(model));
        }
        /// <summary>
        /// 执行SQL语句传入名值对参数
        /// </summary>
        /// <param name="sql"></param>4
        /// <returns></returns>
        public virtual int ExecuteSql(string sql, IDictionary<string, object> parameters)
        {
            int re = -1;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                SetParams(query, parameters);
                re += query.ExecuteUpdate();
                return re;
            });
            return re;
        }

        /// <summary>
        /// 批量执行SQL语句传入名值对参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteSql(string sql, IList<IDictionary<string, object>> paramList)
        {
            int re = -1;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                foreach (IDictionary<string, object> p in paramList)
                {
                    SetParams(query, p);
                    re += query.ExecuteUpdate();
                }
                return re;
            });
            return re;
        }

        /// <summary>
        /// 批量执行SQL语句传入名值对参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteSql(string sql, IList<object> modelList)
        {
            int re = -1;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                foreach (object o in modelList)
                {
                    SetParams(query, GetParams(o));
                    re += query.ExecuteUpdate();
                }
                return re;
            });
            return re;
        }
        /// <summary>
        /// 返回命名语句全部的数据.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetNamedSqlData(string sqlName, IDictionary<string, object> parameters)
        {
            IList<System.Collections.IDictionary> data = null;
            try
            {
                Template.Execute(delegate(ISession session)
                {
                    IQuery query = session.GetNamedQuery(sqlName);
                    SetParams(query, parameters);
                    query.SetResultTransformer(Transformers.AliasToEntityMap);
                    data = query.List<System.Collections.IDictionary>();

                    return data;
                });
            }
            catch(Exception ex)
            {
                
            }
            return data;
        }


        /// <summary>
        /// 返回SQL语句全部的数据.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetSqlData(string sql)
        {
            return GetSqlData(sql, null);
        }
        /// <summary>
        /// 返回SQL语句全部的数据.
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetSqlData(string sql, IDictionary<string, object> parameters)
        {
            IList<System.Collections.IDictionary> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                SetParams(query, parameters);
                query.SetResultTransformer(Transformers.AliasToEntityMap);
                data = query.List<System.Collections.IDictionary>();

                return data;
            });
            return data;
        }

        /// <summary>
        /// 返回SQL语句固定长度的数据
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetSqlData(string sql, int start, int limit)
        {
            return GetSqlData(sql, null, start, limit);
        }

        /// <summary>
        /// 使用原生的SQL语句返回固定长度的数据
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetSqlData(string sql, IDictionary<string, object> parameters, int start, int limit)
        {
            IList<System.Collections.IDictionary> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                SetParams(query, parameters);
                query.SetFirstResult(start);
                query.SetMaxResults(limit);
                query.SetResultTransformer(Transformers.AliasToEntityMap);
                data = query.List<System.Collections.IDictionary>();

                return data;
            });
            return data;
        }
        /// <summary>
        /// 返回固定长度的数据
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetNamedSqlData(string sqlName, int start, int limit)
        {
            return GetNamedSqlData(sqlName, null, start, limit);
        }

        /// <summary>
        /// 返回固定长度的数据
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public virtual IList<System.Collections.IDictionary> GetNamedSqlData(string sqlName, IDictionary<string, object> parameters, int start, int limit)
        {
            IList<System.Collections.IDictionary> data = null;
                Template.Execute(delegate(ISession session)
                {
                    IQuery query = session.GetNamedQuery(sqlName);
                    SetParams(query, parameters);
                    query.SetFirstResult(start);
                    query.SetMaxResults(limit);
                    query.SetResultTransformer(Transformers.AliasToEntityMap);
                    data = query.List<System.Collections.IDictionary>();
                    
                    return data;
                });
            return data;
        }

        public virtual IList<T> GetTypedSqlData<T>(string sql)
        {
            return GetTypedSqlData<T>(sql, null);
        }

        public virtual IList<T> GetTypedSqlData<T>(string sql, int start, int limit)
        {
            return GetTypedSqlData<T>(sql, null, start, limit);
        }

        /// <summary>
        /// 返回固定长度结果集的模型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public virtual IList<T> GetTypedSqlData<T>(string sql, IDictionary<string, object> parameters, int start, int limit)
        {
            IList<T> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                SetParams(query, parameters);
                query.SetResultTransformer(Transformers.AliasToBean<T>());
                query.SetFirstResult(start);
                query.SetMaxResults(limit);
                data = query.List<T>();
                return data;
            });
            return data;
        }

        /// <summary>
        /// 返回SQL语句结果集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<T> GetTypedSqlData<T>(string sql, IDictionary<string, object> parameters)
        {
            IList<T> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.CreateSQLQuery(sql);
                SetParams(query, parameters);
                query.SetResultTransformer(Transformers.AliasToBean<T>());
                data = query.List<T>();
                return data;
            });
            return data;
        }

        /// <summary>
        /// 返回结果集的模型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName)
        {
            return GetTypedSqlNameData<T>(sqlName, null);
        }
        /// <summary>
        /// 返回结果集的模型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName, IDictionary<string, object> parameters)
        {
            IList<T> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.GetNamedQuery(sqlName);
                SetParams(query, parameters);
                query.SetResultTransformer(Transformers.AliasToBean<T>());
                data = query.List<T>();

                return data;
            });
            return data;
        }

        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName, object model)
        {
            return GetTypedSqlNameData<T>(sqlName, GetParams(model));
        }

        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName, int start, int limit)
        {
            return GetTypedSqlNameData<T>(sqlName, null, start, limit);
        }
        /// <summary>
        /// 返回固定长度结果集的模型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName, IDictionary<string, object> parameters, int start, int limit)
        {
            IList<T> data = null;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.GetNamedQuery(sqlName);
                SetParams(query, parameters);
                query.SetFirstResult(start);
                query.SetMaxResults(limit);
                query.SetResultTransformer(Transformers.AliasToBean<T>());
                data = query.List<T>();

                return data;
            });
            return data;  
        }

        public virtual IList<T> GetTypedSqlNameData<T>(string sqlName, object model, int start, int limit)
        {
            return GetTypedSqlNameData<T>(sqlName, GetParams(model), start, limit);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="query"></param>
        /// <param name="key"></param>
        /// <param name="o"></param>
        public void SetParamValue(IQuery query, string key, object o)
        {
            if (o is string || o is Guid)
            {
                query.SetString(key, o.ToString());
            }
            else if (o is long)
            {
                query.SetInt64(key, (long)o);
            }
            else if (o is int)
            {
                query.SetInt32(key, (int)o);
            }
            else if (o is double)
            {
                query.SetDouble(key, (double)o);
            }
            else if (o is Single)
            {
                query.SetSingle(key, (Single)o);
            }
            else if (o is bool)
            {
                query.SetBoolean(key, (bool)o);
            }
            else if (o is DateTime)
            {
                query.SetTimestamp(key, (DateTime)o);
            }
            else if (o == null || o is DBNull)
            {
                //query.SetParameter(key, null);
                //query.set
                query.SetString(key, string.Empty);
            }
            else if(o is byte[])
            {
                query.SetBinary(key, (byte[]) o);
            }
            //else if (o is Guid)
            //{
            //    query.SetGuid(key, (Guid)o);
            //}
            else
            {
                query.SetString(key, o.ToString());
            }
        }

        /// <summary>
        /// 寻找用户输入的参数的集合, 转换成对应的类型. 设置到Query里.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="param"></param>
        protected void SetParams(IQuery query, IDictionary<string, object> param)
        {
            if (param != null)
            {
                //foreach (string key in param.Keys)
                foreach(string key in query.NamedParameters)
                {
                    if (!param.ContainsKey(key)) throw new Exception("缺少传入的参数。 :" + key);
                    SetParamValue(query, key, param[key]);
                }
            }
        }//end method
        /// <summary>
        /// 移除SQL语句中的ORDER BY语句。 有些数据库中不支持嵌套查询中内部含有order by 语句。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual string RemoveOrderBy(string sql)
        {
            int startIndex = sql.ToLower().IndexOf("order by");
            if (startIndex == -1) return sql;

            int count = sql.IndexOf(")", startIndex);
            count = count == -1 ? sql.Length : count;
            sql = sql.Remove(startIndex, count - startIndex);
            return RemoveOrderBy(sql);
        }//end method

        /// <summary>
        /// 取得实体中全部的共有属性， 返回属性的名值对集合。 
        /// 可以充当执行SQL语句的参数。
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public virtual IDictionary<string, object> GetParams(object o)
        {
            IDictionary<string, object> param = new Dictionary<string, object>();
            if (o != null)
            {
                Type t = o.GetType();
                PropertyInfo[] propertys = t.GetProperties();
                foreach (PropertyInfo p in propertys)
                {
                    if (p.CanRead)
                    {
                        param.Add(p.Name.ToLower(), p.GetValue(o, null));
                    }
                }
            }
            return param;
        }
        /// <summary>
        /// 返回数据库类型对应的语言类型
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public DbType DbTypeMapping(Type t)
        {
            if (t == typeof(string))
            {
                return DbType.String;
            }
            else if (t == typeof(int))
            {
                return DbType.Int32;
            }
            else if (t == typeof(decimal))
            {
                return DbType.Decimal;
            }
            else if (t == typeof(DateTime))
            {
                return DbType.DateTime;
            }
            else if(t == typeof(byte[]))
            {
                return DbType.Binary;
            }
            return (DbType)Enum.Parse(typeof(DbType), t.Name, true);
        }

        /// <summary>
        /// 检查数据库中是否含有传入的数据表
        /// </summary>
        /// <param name="tablename">The tablename.</param>
        /// <returns></returns>
        public bool CheckHasTable(string tablename)
        {
            bool result = false;
            Template.Execute(delegate(ISession session)
            {
                IQuery query = session.GetNamedQuery("checktable");
                int r = session
                    .CreateSQLQuery(query.QueryString.Replace("@table", tablename))
                    .UniqueResult<int>();
                result = r == 1;
                return null;
            });
            return result;
        }
    }
}
